--------------------------------------------------------------------------------
-- @name: SQL Trace time
-- @author: dion cho
-- @note: test case for the time error in tkprof
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

----------------------------------------------------
-- Case 1: cpu time vs. elapsed time = getrusage vs. gettimeofday
drop table t1 purge;

create table t1
as 
select level as c1, level as c2, level as c3, level as c4, level as c5
from dual
connect by level <= 5000;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /* 1st */ * from t1;

select /* 2nd */ * from t1;

select /* 3rd */ * from t1;

select /* 4th */ * from t1;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));


------------------------------------------------------
-- Case 2: cpu wait
-- @temp.sql
--begin
--	for idx in 1 .. 10000 loop
--		for r in (select * from dual) loop
--			null;
--		end loop;
--	end loop;
--end;
--/
--
--exit;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 1);

begin
	for idx in 1 .. 50000 loop
		for r in (select 1 from dual) loop
			null;
		end loop;
	end loop;
end;
/

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));


-- 11 sessions
exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp
ho start sqlplus tpack/tpack@ukja1021 @temp

begin 
	for idx in 1 .. 10 loop
		tpack.signal;
	end loop;
end;
/

begin
	for idx in 1 .. 50000 loop
		for r in (select 1 from dual) loop
			null;
		end loop;
	end loop;
end;
/

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));


-----------------------------------------------------------------------------
-- Case 3: cursor#0

-- LOB!
drop table t1 purge;

create table t1(c1 int, c2 clob);

declare
    v_clob clob := rpad('x', 4000, 'x');
begin
    for idx in 1 .. 10000 loop
        insert into t1 values(idx, v_clob);
    end loop;
end;
/

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

set autot trace

set timing on
select * from t1;
set timing off

set autot off

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));


exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

set autot trace

set timing on
select * from t1;
set timing off

set autot off

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid')));


@capture_off
